-- Create the databases and tables
CREATE DATABASE IF NOT EXISTS column_test;

use column_test
go
-- Table structure for columns
-- id, 名字，在看人数，总共篇章数，详情描述-简介，创建时间，修改时间，删除时间
DROP TABLE IF EXISTS `column`;
CREATE TABLE `column`(
    `id` int NOT NULL AUTO_INCREMENT COMMENT '专栏ID',
    `column_name` varchar(255) NOT NULL COMMENT '专栏故事名',
    `column_reader_nums` int NOT NULL COMMENT '在读人数',
    `column_detail` varchar(255) NOT NULL COMMENT '内容详情',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    `deleted_at` datetime NULL,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8mb4;

-- Table structure for chapters
-- id, 专栏id, 作者id, 标题， 内容， 创建时间，修改时间，删除时间
DROP TABLE IF EXISTS `chapter`;
CREATE TABLE `chapters`(
    `id` int NOT NULL AUTO_INCREMENT COMMENT '章节ID',
    `column_id` int NOT NULL COMMENT '专栏ID',
    `author_id` int NOT NULL COMMENT '作者ID',
    `chapter_title` varchar(255) NOT NULL COMMENT '标题',
    `chapter_content` text NOT NULL COMMENT '本章节内容',
    `created_at` datetime NOT NULL,
    `updated_at` datetime NOT NULL,
    `deleted_at` datetime NULL,
    PRIMARY KEY(`id`)
) ENGINE=InnoDB AUTO_INCREMENT=1 DEFAULT CHARACTER SET=utf8mb4;

-- Insert data into tables
INSERT INTO `column` (`column_name`, `column_reader_nums`, `column_detail`, `created_at`, `updated_at`)
VALUES ('生活如此简单',  5, '关于生活的小说', NOW(), NOW());

INSERT INTO `chapter` (`column_id`, `author_id`,`chapter_title`, `chapter_content`, `created_at`, `updated_at`)
VALUES (1, 1, '1-章', 'This is chapter 1', NOW(), NOW()),
       (1, 1, '2-章', 'This is chapter 2', NOW(), NOW()),
       (1, 1, '3-章', 'This is chapter 3', NOW(), NOW()),
       (1, 1, '4-章', 'This is chapter 4', NOW(), NOW()),
       (1, 1, '5-章', 'This is chapter 5', NOW(), NOW());


